﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Jumpton.SFA.Server.Entity;
using Jumpton.Persistent;
using Jumpton.Data;
using System.Data;
using Jumpton.SFA.Server.BusinessRule.System;
using Jumpton.SFA.Server.BusinessRule.MasterData;

namespace Jumpton.SFA.Server.BusinessRule.CallRoute
{
    public class CustomerRouteManager : BusinessLogic
    {
        public ObjectTable GetRouteCustomer(decimal routeID)
        {
            return new ObjectTable(typeof(ROUTE_CUSTOMERS), "this.ROUTENUMBER='" + routeID + "'");
        }

        public ObjectTable GetCustomerByUser(decimal UserID, string updatetime)
        {
            return new ObjectTable(typeof(CustomerView), string.Format("ID in (select Customer_ID from route_customers where ROUTE_NUMBER=0 and user_id='{0}') and a.LAST_UPDATE_TIME > '{1}'", UserID, updatetime));
        }
        public DataTable GetDeleteCustomerByUser(string UserID,string Datetime)
        {
            string sql = @"SELECT CUSTOMERID FROM DELETE_RECORD WHERE USERID ='" + UserID + "' AND LAST_UPDATE_TIME>'"+Datetime+"' AND CUSTOMERID NOT IN (SELECT CUSTOMER_ID FROM VISITS WHERE USER_ID='" + UserID + "' AND LAST_UPDATE_TIME>'" + Datetime + "' )";
            return SqlData.OpenSql(sql).Tables[0];
        }

        public bool ExistCustomer(decimal ID)
        {
            return new ObjectTable(typeof(CustomerView), "ID in (select distinct ID from CUSTOMERS where ID=" + ID + ")").Count > 0 ? true : false;
        }
        public DataTable GetUsersForOrder(decimal customerID)
        {
            string sql = @"SELECT	DISTINCT
		                            USERS.ID
		                            ,PERSONS.NAME
                            FROM ROUTE_CUSTOMERS RC
	                            INNER JOIN USERS
		                            ON RC.USER_ID=USERS.ID
	                            INNER JOIN PERSONS
		                            ON PERSONS.ID=USERS.PERSON_ID
                            WHERE RC.ROUTE_NUMBER='0' AND RC.CUSTOMER_ID='" + customerID + "'";

            sql = sql + @"       UNION
                            SELECT   DISTINCT
                                    USERS.ID
                                    ,PERSONS.NAME
                            FROM USERS
                                INNER JOIN PERSONS
                                    ON USERS.PERSON_ID=PERSONS.ID
                                INNER JOIN DictionaryItems
                                    ON PERSONS.POSITION=DictionaryItems.DictionaryItemID
                            WHERE DictionaryItems.Name<>'业务员' AND USERS.ORG_ID IN (select org_id from customers where id='" + customerID + "')";


            return SqlData.OpenSql(sql).Tables[0];
        }

        public DataTable GetUsersByCustomer(decimal customerID)
        {
            string sql = @"SELECT	DISTINCT
		                            USERS.ID
		                            ,PERSONS.NAME
                            FROM ROUTE_CUSTOMERS RC
	                            INNER JOIN USERS
		                            ON RC.USER_ID=USERS.ID
	                            INNER JOIN PERSONS
		                            ON PERSONS.ID=USERS.PERSON_ID
                            WHERE RC.ROUTE_NUMBER='0' AND RC.CUSTOMER_ID='" + customerID + "'";

            return SqlData.OpenSql(sql).Tables[0];
        }


        public ROUTE_CUSTOMERS Load(decimal routeCustomerID)
        {
            return (ROUTE_CUSTOMERS)ObjectRow.Load(typeof(ROUTE_CUSTOMERS), string.Format("this.ID='{0}'", routeCustomerID));
        }

        public ROUTE_CUSTOMERS Load(decimal userID, decimal routeNumber, decimal routeCustomerID)
        {
            return (ROUTE_CUSTOMERS)ObjectRow.Load(typeof(ROUTE_CUSTOMERS), string.Format("this.USERID ='{0}' and this.ROUTENUMBER ='{1}' and this.CUSTOMERID='{2}'", userID, routeNumber, routeCustomerID));
        }

        public ObjectTable GetAllRouteCust(decimal userID)
        {
            return new ObjectTable(typeof(ROUTE_CUSTOMERS), "this.USERID='" + userID + "'");
        }

        public static ROUTES CreateRoute(ROUTES route)
        {
            //判断是否存在该路线，如果不存在则允许创建，否则不允许创建
            object number = SqlData.ExecuteScalar("SELECT id FROM [ROUTES] WHERE [USER_ID] =" + route.USERID + " AND [ROUTE_NUMBER] =" + route.ROUTENUMBER);
            if (number != null && number != DBNull.Value)
            {
                return new RouteManager().Load(decimal.Parse(number.ToString()));

            }
            route.Save();
            return route;
        }
        public static DataSet SelectRouteList(decimal userID, string valid)
        {
            return SqlData.OpenSql(String.Format(@"SELECT 
	            R.DESCRIPTION ,
	            RC.ROUTE_NUMBER ,
	            C.ID,
	            RC.SEQUENCE ,
	            C.NAME ,
	            (SELECT Name FROM DictionaryItems WHERE DictionaryItemID = C.LOCAL_LEVEL1_CODE) AS Channel ,
	            CI.ADDRESS_LINE,
	            RC.active
             FROM ROUTE_CUSTOMERS AS RC 
             INNER JOIN ROUTES AS R ON RC.USER_ID = R.USER_ID AND RC.ROUTE_NUMBER = R.ROUTE_NUMBER 
             INNER JOIN CUSTOMERS C ON RC.CUSTOMER_ID = C.ID  
             LEFT JOIN CONTACT_INFO CI ON C.ID = CI.CUSTOMER_ID
             WHERE RC.ROUTE_NUMBER <>''0'' AND RC.USER_ID ={0} {1}
              ORDER BY RC.ROUTE_NUMBER , RC.SEQUENCE '", userID, String.IsNullOrEmpty(valid) ? "" : " AND RC.active='" + valid + "'"));
        }
        public static DataSet SearchCustomerForUser(decimal userID, string Assigned, decimal domainID,decimal orgid,string strCondition)
        {
            return SqlData.OpenSP("usp_SelectCustomersForUser", userID, Assigned,domainID,orgid, strCondition);
        }

        public static DataSet SearchCustomerForRoute(decimal userID, decimal routeNumber, string strCondition)
        {
            return SqlData.OpenSP("usp_SelectCustomersForRoute", userID, routeNumber, strCondition);
        }

      
        public static DataSet SelectUserCustomer(int userID, decimal domainID, int orgID)
        {
            return SqlData.OpenSP("usp_SelectUserCustomers", userID, domainID, orgID);
        }

        public static void TransferCustomerToAnotherUser(decimal rcid, decimal userid, decimal customerid, decimal newUserID)
        {
            SqlData.ExecuteSP("sp_TransferCustomerToAnotherUser", rcid, userid, customerid.ToString(), newUserID);
        }

        public static DataSet GetCurrentDayOfWeek(decimal routeNumber)
        {
            string Query = string.Format("select dbo.f_GetCurrentDayOfWeek({0})", routeNumber);
            return SqlData.OpenSql(Query);
        }

        public static void AssignCustomerForUser(bool insertFlag, bool updateFlag, decimal RCID, ROUTE_CUSTOMERS routeCustomer, decimal domainID)
        {
            SqlData.OpenSP("sp_AssignCustomerForUser", insertFlag,
                                                        updateFlag,
                                                        RCID,
                                                        routeCustomer.ID,
                                                        routeCustomer.USERID,
                                                        routeCustomer.ROUTENUMBER,
                                                        routeCustomer.CUSTOMERID.ToString(),
                                                        0,
                                                        routeCustomer.LAST_UPDATE_TIME,
                                                        domainID);
        }

       

        /// <summary>
        /// 根据各种情况操作操作业代的线路
        /// </summary>
        /// <param name="insertFlag"></param>
        /// <param name="updateFlag"></param>
        /// <param name="RCID"></param>
        /// <param name="routeCustomer"></param>
        /// <returns>失败的客户名称</returns>
        public static void AssignCustomerForRoute(bool insertFlag, bool updateFlag, decimal RCID, ROUTE_CUSTOMERS routeCustomer)
        {
            //********************************
            //**Coder:washing
            //**Date:09-06-19
            //**Content:顺序号的修改说明:由于需要在UPDATE或者INSERT 线路时，将新的路线插在
            //**        之前的线路之后。所以做了对路线的任何修改，都会将顺序号变成999的操作。
            //**        
            //********************************

            //********************************
            //**Coder:washing
            //**Date:09-06-20
            //**Content:置为无效的路线修改为删除该路线
            //********************************

            if (insertFlag && !updateFlag)
            {

                //insert 顺序号写死插入999
                SqlData.ExecuteSP("sp_InsertCustomerForRoute", routeCustomer.USERID,
                                                            routeCustomer.ROUTENUMBER,
                                                            routeCustomer.CUSTOMERID.ToString(),
                                                            routeCustomer.LAST_UPDATE_TIME);

            }
            else
            {
                if (!insertFlag && updateFlag)
                {
                    //DELETE 
                    string SQL = string.Format(" DELETE [dbo].[ROUTE_CUSTOMERS] WHERE ID = {0}", RCID);
                    SqlData.OpenSql(SQL);
                }
                else
                {
                    if (insertFlag && updateFlag)
                    {
                        //UPDATE 
                        SqlData.OpenSP("sp_UpdateCustomerForRoute", RCID,
                                                                    routeCustomer.ROUTENUMBER,
                                                                    routeCustomer.SEQUENCE
                                                                   );
                    }
                }
            }

        }
        public static string GetTempVaildDateByUser(decimal userID)
        {
            string sql = "select top 1 VALID_date FROM ROUTE_CUSTOMERS where ROUTE_NUMBER=8 AND USER_ID=" + userID.ToString();
            DataSet ds = SqlData.OpenSql(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {

                return ds.Tables[0].Rows[0]["VALID_date"] == DBNull.Value ? "" : DateTime.Parse(ds.Tables[0].Rows[0]["VALID_date"].ToString()).ToString("yyyy-MM-dd");
            }
            return "";
        }


        public decimal Save(ROUTE_CUSTOMERS routeCustomer)
        {
            if (routeCustomer != null)
            {
                try
                {
                    routeCustomer.Save();
                }
                catch (Exception err)
                {
                    message = err.Message; throw err;
                }
                return routeCustomer.ID;
            }

            return 0;
        }

        public ObjectTable GetRouteCustomerView(decimal userid, decimal routenumber)
        {
            return new ObjectTable(typeof(ROUTE_CUSTOMERS_VIEW), string.Format("USER_ID={0} AND ROUTE_NUMBER={1} ORDER BY RC.SEQUENCE ASC", userid, routenumber));
        }
        public DataTable GetRouteCustomerViewAutoSequence(decimal userid, decimal routenumber)
        {
            string sql = @"SELECT RC.SEQUENCE,RC.ID,C.NAME,CI.ADDRESS_LINE,CI.TELEPHONE,C.IDENTITY1,C.IDENTITY2  FROM ROUTE_CUSTOMERS RC INNER JOIN CONTACT_INFO CI ON CI.CUSTOMER_ID=RC.CUSTOMER_ID
INNER JOIN CUSTOMERS C ON C.ID=CI.CUSTOMER_ID WHERE RC.USER_ID={0} AND RC.ROUTE_NUMBER={1} ORDER BY RC.SEQUENCE ASC";
            sql = string.Format(sql, userid, routenumber);
            DataSet ds = SqlData.OpenSql(sql);

            return ds.Tables[0];
        }
        public ObjectTable GetRouteCustomer(decimal userid, decimal routenumber)
        {
            return new ObjectTable(typeof(ROUTECUSTOMERSVIEW), string.Format("USER_ID={0} AND ROUTE_NUMBER={1} ORDER BY a.SEQUENCE ASC", userid, routenumber));
        }
    }
}
